SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [Live].[Valcom_3Q13]
    @FundID VARCHAR(50) ,
    @NAVDate DATE = NULL ,
    @ReportName VARCHAR(100) = NULL
 AS 
    BEGIN
           
           --REMEMBER TO REMOVE THE SECTION MARKED BY: %%REMOVE  (Just search for %%REMOVE)
           --IT IS A PRICING OVERRIDE FOR 3.31 VALCOM WHERE PRICING
           --DID NOT EXIST IN ARTEMIS YET!!!!!
        SET FMTONLY ON;
        DECLARE @fmtonlyON BIT
        SET @fmtonlyON = 0
        IF ( 1 = 0 ) 
            BEGIN
                SET @fmtonlyON = 1
            END
        SET FMTONLY OFF;
           
           
        SET NOCOUNT ON;
            IF 1 = 2 
            SELECT  NULL AS column1
           
           
           --DECLARE @FundID VARCHAR(50)
           -- DECLARE   @NAVDate DATE = NULL
           --SET @FundID = 'FSIC'
           --SET @NAVDate ='6/28/2013'
           
        DECLARE @PriorNAVDATE DATE
        DECLARE @LastValcomExportID INT
        DECLARE @LastValcomDate DATE
        DECLARE @Cash DECIMAL(30, 2)
        DECLARE @netlevamt DECIMAL(30, 2)
        DECLARE @fundlevyld DECIMAL(30, 10)
           
           
           
        DECLARE @ExportID INT
        SET @ExportID = CASE WHEN @NAVDate IS NULL
                             THEN ( SELECT  MAX(ex.ExportID)
                                    FROM    [FSFundModel].[Reporting].Export ex
                                    WHERE   ex.FundId = @FundID
                                  )
                             ELSE ( SELECT  MAX(ex.ExportID)
                                    FROM    [FSFundModel].[Reporting].Export ex
                                    WHERE  ex.FundId= @FundID
                                            AND  ex.NAVDate = @NAVDate
											 
                                  )
                        END 
  
                        

		   
        SET @LastValcomDate = ( SELECT  MAX(e.NAVDate)
                                FROM    [FSFundModel].[Reporting].Export e
                                WHERE   e.FundId = @FundID
                                        AND e.CreatedBy = 'VALCOMM'
                              )

		
     
		
	      SET @LastValcomExportID = ( SELECT  MAX(e.ExportId)
                                    FROM    [FSFundModel].[Reporting].Export e
                                    WHERE   e.FundId = @FundID
                                            AND e.CreatedBy = 'VALCOMM'
											AND e.NAVDate = @LastValcomDate
                                  )
           

		   IF (@FundID = 'FSEPF')
		   BEGIN
				SET @LastValcomDate = '6/28/2013'
				SET @LastValcomExportID = 859
			END		         
           
        IF ( @NAVDate IS NULL ) 
            BEGIN
                SELECT  @NAVDate = e.NAVDate
                FROM    [FSFundModel].[Reporting].Export e
                WHERE   e.ExportID = @ExportID
                        AND FundID = @FundID
            END
           
       
         
           
        SELECT  @PriorNAVDate = PrevNAVDate ,
                @cash = CashAdj ,
                @netlevamt = netlevamt ,
                @fundlevyld = Levyld
        FROM    [FSFundModel].[Reporting].fund f
        WHERE   f.exportid = @exportid
                AND f.CurrNAVDate = @NAVDate
                AND f.FundId = @FundID
  
	DECLARE @RESULTS TABLE 
            (
              PortfolioID VARCHAR(50) ,
              PortfolioName VARCHAR(50) ,
              InstrClass VARCHAR(100) ,
              InstrName VARCHAR(200) ,
              IssuerName VARCHAR(200) ,
              OrigPurchDate DATETIME ,
              IsQualified VARCHAR(2) ,
              AssetQualityRating INT ,
              PricingDepth VARCHAR(10) ,
              EstimateDuration DECIMAL(38, 12) ,
              Moody_Rating VARCHAR(50) ,
              Portfolio_Moody_Rating VARCHAR(50) ,
              Fund_Moody_Rating VARCHAR(50) ,
              GAAP_Pur VARCHAR(50) ,
              GAAP_Cur VARCHAR(50) ,
              GAAP_Walk VARCHAR(50) ,
              Sum_Par DECIMAL(38, 12) ,
              Amort_Cost_Pct_Par VARCHAR(50) NULL,
              Weight_Price VARCHAR(50) ,
              PriorNAV_Price DECIMAL(38, 12) ,
              NAVDate_Price DECIMAL(38, 12) ,
              Spread VARCHAR(50) ,
              Ask DECIMAL(38, 12) ,
              Bid DECIMAL(38, 12) ,
              Amort_Cost DECIMAL(38, 12) ,
              MktVal DECIMAL(38, 12) ,
              MktVal_Pct DECIMAL(38, 12) ,
              Net_Unreal_App DECIMAL(38, 12) ,
              Pct_Net_Unreal_Gain VARCHAR(50) ,
              DailyVal_Change DECIMAL(38, 12) ,
              PriceChange_Pct VARCHAR(50) ,
              SecurityID VARCHAR(50) ,
              Sum_Unreal_Dep DECIMAL(38, 12) ,
              Sum_Unreal_App DECIMAL(38, 12) ,
              Prop_Reason VARCHAR(50) ,
              Prop_Track VARCHAR(50) ,
              PricingMethod VARCHAR(50) ,
              TotalInt_Purch_Price DECIMAL(38, 12) ,
              TotalInt_Cur_Price DECIMAL(38, 12) ,
              Sum_OrigCost DECIMAL(38, 12) ,
              Security_Type VARCHAR(50) ,
              Issuer_Name VARCHAR(50) ,
              Lots DECIMAL(38, 12) ,
              Iss_SubSector VARCHAR(100) ,
              Iss_Sector VARCHAR(100) ,
              ExternalID VARCHAR(20) ,
              FocusGroup VARCHAR(5) ,
              FocusReason VARCHAR(100) ,
              portSortOrder INT ,
              instSortOrder INT ,
              priorMoodyRating VARCHAR(5) ,
              InstrumentID INT ,
              Maturity_Date DATETIME ,
              Settle_Disc_prch DECIMAL(38, 12) ,
              Pik_Prch DECIMAL(38, 12) ,
              Coupon_Purch DECIMAL(38, 12)
            )
 
		INSERT INTO @Results 
		SELECT 
		 vb.PortfolioID ,
		        vb.PortfolioName ,
		        vb.InstrClass ,
		        vb.InstrName ,
		        vb.IssuerName ,
		        vb.OrigPurchDate ,
		        vb.IsQualified ,
		        vb.AssetQualityRating ,
		        vb.PricingDepth ,
		        vb.EstimateDuration ,
		        vb.Moody_Rating ,
		        vb.Portfolio_Moody_Rating ,
		        vb.Fund_Moody_Rating ,
		        vb.GAAP_Pur ,
		        vb.GAAP_Cur ,
		        vb.GAAP_Walk ,
		        vb.Sum_Par ,
		        vb.Amort_Cost_Pct_Par ,
		        vb.Weight_Price ,
		        vb.PriorNAV_Price ,
		        vb.NAVDate_Price ,
		        vb.Spread ,
		        vb.Ask ,
		        vb.Bid ,
		        vb.Amort_Cost ,
		        vb.MktVal ,
		        vb.MktVal_Pct ,
		        vb.Net_Unreal_App ,
		        vb.Pct_Net_Unreal_Gain ,
		        vb.DailyVal_Change ,
		        vb.PriceChange_Pct ,
		        vb.SecurityID ,
		        vb.Sum_Unreal_Dep ,
		        vb.Sum_Unreal_App ,
		        vb.Prop_Reason ,
		        vb.Prop_Track ,
		        vb.PricingMethod ,
		        vb.TotalInt_Purch_Price ,
		        vb.TotalInt_Cur_Price ,
		        vb.Sum_OrigCost ,
		        vb.Security_Type ,
		        vb.Issuer_Name ,
		        vb.Lots ,
		        vb.Iss_SubSector ,
		        vb.Iss_Sector ,
		        vb.ExternalID ,
		        vb.FocusGroup ,
		        vb.FocusReason ,
		        vb.portSortOrder ,
		        vb.instSortOrder ,
		        vb.priorMoodyRating ,
		        vb.InstrumentID ,
		        vb.Maturity_Date ,
		        vb.Settle_Disc_prch ,
		        vb.Pik_Prch ,
		        vb.Coupon_Purch

		 FROM Live.ValcomBase(@ExportID, @FundID) vb
	


 

  UPDATE  r
        SET    r.PriorNAV_Price = coalesce( pp.mid, r.Weight_Price)
        FROM    @Results r
		LEFT JOIN  dbo.fnGetSpecificNAVDatePriceTBL_VALCOM(@LastValcomDate,0) pp
		ON pp.LXID = r.SecurityId


 

        DECLARE @totalMktVal DECIMAL(38, 12)
        SET @totalMktVal = ( SELECT SUM(MktVal)
                             FROM   @results
                           )
        UPDATE  r
        SET     r.MktVal_Pct = r.MktVal / @totalMktVal
        FROM    @results r
 
      
        IF ( @fundID LIKE 'FSIC%' ) 
            BEGIN
                UPDATE  r
                SET     r.priorMoodyRating = inst.MoodysRating ,
                        r.PriorNAV_Price = a.TodayPrice
                FROM    @results r
                        INNER JOIN FSFundModel.Reporting.Asset a 
						ON r.InstrumentID  = a.InstrumentId
					    INNER JOIN [FSFundModel].[Reporting].Instrument inst 
						 ON r.InstrumentID = inst.InstrumentId
						 AND a.ExportId = inst.ExportId 
                        
                        
                WHERE   inst.ExportId = @LastValcomExportID
                 --Moodys Rating
                        --AND a.IsActiveEndOfDay = 1
 
            END

 
 
        UPDATE  r
        SET     r.pricingDepth = CASE WHEN markit.depth = 'implied' THEN '1'
                                      ELSE markit.depth
                                 END ,
                r.Ask = markit.closeoffer ,
                r.Bid = markit.closebid ,
                r.spread = ( markit.closeoffer - markit.closebid )
        FROM    @results r
                INNER JOIN [FSPricing].dbo.Pricing_MarkitLoans_Marks markit ON r.securityID= markit.LoanXID
        WHERE   
				r.securityID = markit.LoanXID
				AND 
                markit.MarkDate = @NAVDate
 
        UPDATE  r
        SET     r.pricingDepth = MB.numQuotes ,
                r.Ask = MB.AskPrice ,
                r.Bid = MB.BidPrice ,
                r.Spread = ( MB.askPrice - MB.bidPrice )
        FROM    @results r
                INNER JOIN [FSPricing].dbo.Pricing_MarkitBonds mb ON r.SecurityID = mb.ISIN
        WHERE   
		r.SecurityID = mb.ISIN
		AND   mb.PriceDate = @NAVDate
 
				
 
        UPDATE  r
        SET     r.pricingDepth = '1' ,
                r.Ask = thom.Offer ,
                r.Bid = thom.bid ,
                r.Spread = ( thom.offer - thom.bid )
        FROM    @results r
                INNER JOIN FSPricing.dbo.Pricing_ThomsonCLOs thom ON r.SecurityID = thom.FSIdentifier
        WHERE   
		 r.SecurityID = thom.FSIdentifier AND 
                 thom.PriceDate = @NAVDate
 
 
        UPDATE  r
        SET     r.pricingdepth = ISNULL(r.pricingdepth, '1')
        FROM    @results r
 
        UPDATE  r
        SET     r.pricingdepth = '1'
        FROM    @results r
        WHERE   r.pricingdepth = '0'
 
 
 
        UPDATE  r
        SET     r.spread = ISNULL(r.spread, r.ask - r.bid)
        FROM    @results r
        WHERE   r.instrclass NOT LIKE '%Equity%'
 
 
 
 UPDATE r

 SET	PriorNAV_Price = p.Mid

 FROM @results r 
 INNER JOIN  FSFundModel.Reporting.Pricing p 
 ON r.SecurityID = p.PricingId 
 WHERE p.ExportId = @LastValcomExportID 
 AND p.PriceDate =  @LastValcomDate

 
        IF ( @ReportName = 'FundTotals' ) 
            BEGIN
                SELECT  f.GAAPYldCurrPrc ,
                        f.PurchPrc ,
                        f.MoodysRating ,
                        f.TodayPrice ,
                        f.EndAdjAmt ,
                        f.AmortCost ,
                        f.MktVal
                FROM    [FSFundModel].[Reporting].Fund f
                WHERE   f.ExportId = @ExportId
            END
 
 

 
        IF ( @fundID = 'FSIC' ) 
            BEGIN
      
                UPDATE  r
                SET     r.priorMoodyRating = inst.moodysrating
                FROM    @results r
                        INNER JOIN [FSFundModel].[Reporting].Instrument inst ON r.instrumentID = inst.InstrumentId
                WHERE   inst.ExportId = @LastValcomExportID
      --Moodys Rating
 
            END
 
 
        IF ( @ReportName = 'Top_20' ) 
            BEGIN
                DECLARE @Top20Issuer TABLE
                    (
                      IssuerID INT ,
                      MktVal DECIMAL(38, 12) ,
                      IssuerName VARCHAR(500)
                    )
 
                INSERT  INTO @Top20Issuer
                        ( IssuerID ,
                          MktVal ,
                          IssuerName
                        )
                        SELECT TOP 20
                                fi.IssuerID ,
                                fi.MktValLT ,
                                i.IssuerName
                        FROM    [FSFundModel].[Reporting].FundIssuer fi
                                INNER JOIN [FSFundModel].[Reporting].Issuer i ON fi.ExportID = i.ExportID
                                                              AND fi.IssuerID = i.IssuerID
                        WHERE   fi.ExportID = @ExportID
                                AND fi.FundId = @FundID
                        ORDER BY fi.MktValLT DESC
 
 
                SELECT  r.IssuerName ,
                        r.InstrName ,
					
                        r.GAAP_Cur ,
                        r.Weight_Price ,
                        r.OrigPurchDate ,
                        r.PricingDepth ,
                        r.Moody_Rating ,
                        CASE WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS PriorNAVDate_Price ,
                        ISNULL(r.Spread, 'N/A') AS Spread ,
                        r.NAVDate_Price AS NAVDate_Price ,
                        r.Sum_Par ,
                        r.Amort_Cost ,
                        i.MktVal AS Issuer_MktVal ,
                        r.MktVal AS Instrument_MktVal ,
                        CASE WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS PrevQPrice ,
                        CASE WHEN r.bid IS NULL THEN 0.0
                             ELSE r.bid
                        END AS Bid ,
                        CASE WHEN r.ask IS NULL THEN 0.0
                             ELSE r.ask
                        END AS ask,
						r.AssetQualityRating
          
                FROM    @results r
                        INNER JOIN @Top20Issuer i ON r.Issuer_Name = i.IssuerName
                WHERE   	r.InstrClass NOT LIKE  '%Unfunded%'
            -- fix to remove unfunded in t20
                ORDER BY i.MktVal DESC ,
                        instSortOrder ,
                        r.instrname
            END
 
        IF ( @ReportName LIKE 'ValComm_PortfolioTotal' ) 
            BEGIN
      
                SELECT  mid.FS_PortAbbrev AS PortfolioID ,
                        pf.PortfolioName ,
                        pf.[GAAPYldCurrPrc] ,
                        pf.[PurchPrc] ,
                        pf.[MoodysRating] ,
                        pf.[TodayPrice] ,
                        pf.[EndAdjAmt] ,
                        pf.[AmortCost] ,
                        pf.[MktVal] ,
                       -- pPreviousValcom.TodayPrice AS PrevPrice,
					   pf.TodayPrice AS PrevPrice,
						pf.[PortAllocPct] 
                FROM    [FSFundModel].Reporting.Portfolio pF
                        INNER JOIN dbo.mapSSB_PortID mID ON mID.ART_PortfolioID = pF.PortfolioId 
                        --,[FSFundModel].Reporting.Portfolio pPreviousValcom
                WHERE   pf.ExportID = @ExportID
                        AND pf.FundID = @FundID		
      --                  AND pPreviousValcom.ExportId = @LastValcomExportID
						--AND pPreviousValcom.PortfolioId = pf.PortfolioId
			


		/* FIXED 

						 SELECT  mid.FS_PortAbbrev AS PortfolioID ,
                        pf.PortfolioName ,
                        pf.[GAAPYldCurrPrc] ,
                        pf.[PurchPrc] ,
                        pf.[MoodysRating] ,
                        pf.[TodayPrice] ,
                        pf.[EndAdjAmt] ,
                        pf.[AmortCost] ,
                        pf.[MktVal] ,
                         COALESCE( pPreviousValcom.TodayPrice, pf.PurchPrc) AS PrevPrice,
						pf.[PortAllocPct] 
                FROM    [FSFundModel].Reporting.Portfolio pF
                        INNER JOIN dbo.mapSSB_PortID mID ON mID.ART_PortfolioID = pF.PortfolioId 
                        OUTER APPLY	(SELECT * FROM  [FSFundModel].Reporting.Portfolio p
					 WHERE p.ExportId = @LastValcomExportID
					 AND p.PortfolioId =pf.PortfolioId 
					   ) pPreviousValcom
                WHERE   
                         pf.ExportID = @ExportID
                        AND pf.FundID = @FundID 


	  */
            END
 
 ------------------------------------
 -- Valuation Committee Summary
 ------------------------------------
 
        IF ( @ReportName LIKE 'ValComm_Summary%' ) 
            BEGIN
                IF ( @ReportName <> 'ValComm_Summary_Focus' ) 
                    BEGIN
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                                   THEN r.instrname + '(6)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.pricingmethod LIKE '%VRC%' AND r.pricingmethod NOT LIKE '%Cost%'
                                                   THEN r.instrname + '(7)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.Issuer_Name IN (
                                                        SELECT DISTINCT
                                                              Issuer_Name
                                                        FROM  @results r
                                                        WHERE r.pricingmethod = 'VRC' )
                                                        AND r.pricingmethod <> 'VRC'
                                                   THEN r.instrname + '(8)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.AssetQualityRating IN (
                                                        4, 5 )
                                                   THEN r.instrname + '(9)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
           
         
                    END 
      
      
            
      
      
                UPDATE  r
                SET     r.focusreason = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                             THEN 'Asset Marked at Cost'
                                             ELSE ''
                                        END
                FROM    @results r
      
      
                UPDATE  r
                SET     r.FocusGroup = CASE WHEN r.AssetQualityRating IN ( '4',
                                                              '5' ) THEN 'Yes'
                                            WHEN r.PricingMethod LIKE '%VRC%'
                                            THEN 'Yes'
                                            WHEN r.PricingMethod LIKE '%Cost%'
                                            THEN 'Yes'
                                            ELSE 'No'
                                       END
                FROM    @results r
      
                UPDATE  r
                SET     r.FocusReason = CASE WHEN r.FocusReason <> 'Asset marked at Cost'
                                             THEN CASE WHEN r.PricingMethod LIKE '%VRC%'
                                                       THEN CASE
                                                              WHEN r.AssetQualityRating = '4'
                                                              THEN 'Level 4 Asset / VRC'
                                                              WHEN r.AssetQualityRating = '5'
                                                              THEN 'Level 5 Asset / VRC'
                                                              ELSE 'VRC'
                                                            END
                                                       ELSE CASE
                                                              WHEN r.assetQualityRating > 3
                                                              THEN 'Level '
                                                              + CAST(r.assetQualityRating AS VARCHAR)
                                                              + ' Asset'
                                                              ELSE ''
                                                            END
                                                  END
                                             ELSE r.focusreason
                                        END
                FROM    @results r
    
	   IF ( @FundID = 'FSIC2' ) 
                    BEGIN
                        UPDATE  @results
                        SET     FocusReason = CASE WHEN ABS(( CAST(NAVDate_Price AS DECIMAL(38,
                                                              10))
                                                              / NULLIF(CAST(PriorNAV_Price AS DECIMAL(38,
                                                              10)), 0) )
                                                            - 1.0000) > 0.0800
                                                   THEN CASE WHEN FocusReason = ''
                                                             THEN 'Price change > 8% QoQ'
                                                             ELSE FocusReason
                                                              + ' / Price change > 8% QoQ'
                                                        END
                                                   ELSE FocusReason
                                              END
					END                                             

	
                UPDATE  r
                SET    -- r.FocusGroup = 'Yes' ,
                        r.FocusReason = 'Other assets priced by VRC'
                FROM    @results r
                WHERE   r.instrName IN (
                        SELECT  r.InstrName
                        FROM    @Results r
                        WHERE   r.Issuer_Name IN (
                                SELECT  r.Issuer_Name FROM @Results r
                                WHERE   r.pricingmethod = 'VRC'
                                INTERSECT
                                SELECT  r.Issuer_Name FROM @Results r
                                WHERE   r.pricingmethod <> 'VRC' ) )
						AND r.focusgroup ='No'

						
                        UPDATE  @results
                        SET     FocusGroup = CASE WHEN ( LEN(FocusReason) >= 3 )
                                                  THEN 'Yes'
                                                  ELSE 'No'
                                             END
                                      
	  
	  ---------------------------------------------
       --LARGE FSIC II SNIPPET [Below] GOES HERE--
	  ---------------------------------------------




	

                                                                                      
                        
				
 
 
 
 
 
 
                SELECT  mID.FS_PortAbbrev AS PortfolioID ,
                        r.PortfolioName ,
                        r.InstrClass ,
                        r.InstrName ,
                        CASE WHEN r.focusgroup = 'Yes' THEN 'TRUE'
                             ELSE 'FALSE'
                        END ,
                        r.OrigPurchDate ,
                        r.SecurityID AS Security_Identifier ,
                        r.IsQualified AS Is_Qualified ,
                        r.AssetQualityRating AS Asset_Quality_Rating ,
                        CASE WHEN r.PricingDepth IS NULL THEN '1'
                             ELSE r.pricingdepth
                        END AS Depth ,
                        r.EstimateDuration AS Estimated_Duration ,
                        r.Moody_Rating AS Moody_Rating ,
                        CASE WHEN r.Spread IS NULL THEN 'N/A'
                             ELSE CAST(r.Spread AS VARCHAR)
                        END AS Spread ,
                        r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price ,
                        r.GAAP_Cur AS GAAP_Yield_At_Current_Price ,
                        r.GAAP_Walk AS Walk_In_GAAP_Yield ,
                        r.sum_par AS Sum_Of_Adj_Par ,
                        r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par ,
                        r.Weight_Price AS Weighted_Avg_Price ,
                        r.PriorNAV_Price AS PriorNAVDate_Price,
                        r.NAVDate_Price AS NAVDate_Price,
                        r.Amort_Cost AS Sum_Of_Amort_Cost ,
                        r.MktVal AS Sum_Of_Mkt_Val ,
                        r.MktVal_Pct AS Percent_of_MarketValue ,
                        r.Net_Unreal_App AS Net_Unrealized_Appreciation ,
                        r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain ,
                        r.DailyVal_Change AS Sum_Of_Daily_Value_Change ,
                        r.PriceChange_Pct AS Price_Chg_Pct ,
                        r.Sum_Unreal_Dep AS Sum_Of_Unreal_Dep ,
                        r.Sum_Unreal_App AS Sum_Of_Unreal_App ,
                        r.Prop_Reason AS Proprietary_Reason ,
                        r.PricingMethod AS Pricing_Method_Type ,
                        r.TotalInt_Purch_Price AS Interest_purchase_price_method ,
                        r.TotalInt_Cur_Price AS Interest_current_price_method ,
                        r.Sum_OrigCost AS Sum_Of_Original_Cost ,
                        r.Security_Type ,
                        r.FocusGroup ,
                        r.FocusReason ,
                        r.portSortOrder ,
                        r.instSortOrder ,
                        r.Issuer_Name ,
                        CASE WHEN r.PriorNAV_Price IS NULL
                             THEN r.PriorNAV_Price
                             WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS prevQuarterPrice ,
                        CASE WHEN r.Bid IS NULL THEN 'N/A'
                             ELSE CAST(r.Bid AS VARCHAR)
                        END AS Bid ,
                        CASE WHEN r.Ask IS NULL THEN 'N/A'
                             ELSE CAST(r.Ask AS VARCHAR)
                        END AS Ask ,
                        CASE 
							 WHEN r.pricingMethod   LIKE '%Cost%'
                             THEN 'Investment Memorandum'
							 WHEN r.pricingMethod LIKE '%Markit%' 
                             THEN 'Markit Screenshot'
                             WHEN r.pricingMethod LIKE '%VRC%'
                             THEN 'Valuation Report'
                            
                        END AS MaterialProvided ,
                        dbo.GtMoodysChange(r.moody_rating, r.priormoodyrating) AS moodyChange ,
                        r.priorMoodyRating ,
                        r.iss_Subsector ,
                        r.prop_Reason ,
                        r.Prop_Track ,
                        r.Maturity_Date ,
                        r.Settle_Disc_prch ,
                        r.Pik_Prch ,
                        r.Coupon_Purch
                FROM    @results r
                        INNER JOIN dbo.mapSSB_PortID mID ON mID.ART_PortfolioID = r.PortfolioId
         
                WHERE   r.FocusGroup = CASE WHEN @ReportName = 'ValComm_Summary_Focus'
                                            THEN 'Yes'
                                            ELSE r.FocusGroup
                                       END
           
                ORDER BY CASE WHEN @ReportName = 'ValComm_Summary'
                                   OR @reportName = 'Valcomm_Summary_Prop'
                              THEN r.portSortOrder
                         END ASC ,
                        r.instsortorder ,
                        r.InstrName ASC
 
            END
 
    END
 
 ----------------------------------------------
 ----- Large FSIC II Snippet ------------------
 ----------------------------------------------
 
 /*
           IF ( @FundID = 'FSIC2' ) 
                    BEGIN
                        UPDATE  @results
                        SET     FocusReason = CASE WHEN ABS(( CAST(NAVDate_Price AS DECIMAL(38,
                                                              10))
                                                              / NULLIF(CAST(PriorNAV_Price AS DECIMAL(38,
                                                              10)), 0) )
                                                            - 1.0000) > 0.0800
                                                   THEN CASE WHEN FocusReason = ''
                                                             THEN 'Price change > 8% QoQ'
                                                             ELSE FocusReason
                                                              + ' / Price change > 8% QoQ'
                                                        END
                                                   ELSE FocusReason
                                              END
           
           
           
                        UPDATE  @results
                        SET     FocusGroup = CASE WHEN ( LEN(FocusReason) > 3 )
                                                  THEN 'Yes'
                                                  ELSE 'No'
                                             END
      
             
           
                        DECLARE @temp TABLE
                            (
                              purchPrice DECIMAL(38, 10) ,
                              Par DECIMAL(38, 10) ,
                              Portfolio VARCHAR(MAX) ,
                              InstID VARCHAR(MAX) ,
                              weightPc DECIMAL(38, 10)
                            )
                        INSERT  INTO @temp
                                ( purchPrice ,
                                  Par ,
                                  Portfolio ,
                                  InstID
                                )
                                SELECT  CASE WHEN a.PurchDate < '3/31/2013'
                                             THEN ic2.[3/31 Price]
                                             ELSE a.PurchPrc
                                        END ,
                                        a.EndAdjAmt ,
                                        a.PortfolioId ,
                                        i.ExtId
                                FROM    FSFundModel.Reporting.Asset a
                                        INNER JOIN FSFundModel.Reporting.Instrument i ON a.ExportId = i.ExportId
                                                              AND a.InstrumentId = i.InstrumentId
                                        INNER JOIN FSFundModel.Reporting.FundCreditInstrument fci ON a.ExportId = Fci.ExportId
                                                              AND fci.InstrumentId = a.InstrumentId
                                                              AND fci.InstrumentId = i.InstrumentId
                                        INNER JOIN dbo.IC2331 ic2 ON i.ExtId = ic2.ID
                                WHERE   a.ExportId = @ExportID
                                        AND a.IsActiveEndOfDay = 1
 
 
 
                        DECLARE @tmp2 TABLE
                            (
                              purchPrice DECIMAL(38, 10) ,
                              Portfolio VARCHAR(MAX) ,
                              InstID VARCHAR(MAX)
                            )
                        INSERT  INTO @tmp2
                                SELECT  SUM(t.Par * t.purchPrice) / SUM(t.par) ,
                                        t.Portfolio ,
                                        t.InstID
                                FROM    @temp t
                                GROUP BY t.Portfolio ,
                                        t.InstID
 
                        UPDATE  @results
                        SET     PriorNAV_Price = t.purchPrice
                        FROM    @tmp2 t
                        WHERE   ( t.Portfolio = PortfolioID )
    
 
                        UPDATE  @results
                        SET     FocusGroup = 'Yes'
                        WHERE   FocusReason <> ''
                    
                UPDATE  @results
                SET     PriorNAV_Price = CASE WHEN PriorNAV_Price IS NULL
                                              THEN PriorNAV_Price
                                              WHEN OrigPurchDate > @LastValcomDate
                                              THEN Weight_Price
                                              ELSE CASE WHEN SecurityID = 'LX126746'

                                                        THEN 90.75
                                                        ELSE PriorNAV_Price
                                                   END
                                         END
              
 END
 

 */
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'PROCEDURE', N'Valcom_3Q13', NULL, NULL
GO
